import pandas as pd
import numpy as np
month = '202411'
# pre_year = '202312'
# pre_month = '202410'
pre_year_int = int(int(month[0:4]) - 1)
pre_year = str(pre_year_int) + '12'
if month[4:6] != '01':
    pre_month = str(int(int(month) - 1))
else:
    pre_month = str(pre_year_int) + '12'
month_int = int(month[4:6])
# 税率
tax_rate = 0.25
# month_int = 11
# Sheet3
# 补充一个参数
xlsx_name = 'D:/repos/sicost/' + month + '/资产总额,负债总额.xlsx'
df_1 = pd.read_excel(xlsx_name)
df_1 = df_1[['SERIAL_NUM', 'COMPANY_NAME', 'CURR_YEAR_VALUE_1', 'CURR_YEAR_VALUE_2']]
df_1['RETURN'] = df_1['CURR_YEAR_VALUE_1'] - df_1['CURR_YEAR_VALUE_2']
df_1.rename(columns={'RETURN': 'CURR_YEAR_RETURN'}, inplace=True)
df_1.drop(['CURR_YEAR_VALUE_1'], axis=1, inplace=True)
df_1.drop(['CURR_YEAR_VALUE_2'], axis=1, inplace=True)
df_1 = df_1.reset_index(drop=False)
df_1.rename(columns={'index': 'INDEX'}, inplace=True)
df_1_1 = df_1[df_1['COMPANY_NAME'] == '非钢铁企业合计']
df_1_1 = df_1_1.reset_index(drop=True)

success = df_1_1.empty is False
if success is False:
    print('空的不需要处理')
else:
    index_tmp = df_1_1.loc[0]['INDEX']
    df_1_2 = df_1[df_1['INDEX'] < index_tmp]
    df_1_2 = df_1_2.reset_index(drop=True)
    df_1 = df_1_2
df_1.drop(['INDEX'], axis=1, inplace=True)
df_1['SERIAL_NUM_STR'] = df_1['SERIAL_NUM'].astype(str)
def __cal_strincludenum(x):
    if '0' in x.SERIAL_NUM_STR:
        rst = 1
    elif '1' in x.SERIAL_NUM_STR:
        rst = 1
    elif '2' in x.SERIAL_NUM_STR:
        rst = 1
    elif '3' in x.SERIAL_NUM_STR:
        rst = 1
    elif '4' in x.SERIAL_NUM_STR:
        rst = 1
    elif '5' in x.SERIAL_NUM_STR:
        rst = 1
    elif '6' in x.SERIAL_NUM_STR:
        rst = 1
    elif '7' in x.SERIAL_NUM_STR:
        rst = 1
    elif '8' in x.SERIAL_NUM_STR:
        rst = 1
    elif '9' in x.SERIAL_NUM_STR:
        rst = 1
    else:
        rst = 0
    return rst


df_1['include_type'] = df_1.apply(lambda x: __cal_strincludenum(x), axis=1)
df_1 = df_1[(df_1['include_type'] == 1) | (df_1['COMPANY_NAME'] == '钢铁企业合计')]
df_1 = df_1.reset_index(drop=True)

df_1.drop(['SERIAL_NUM_STR'], axis=1, inplace=True)
df_1.drop(['include_type'], axis=1, inplace=True)


xlsx_name = 'D:/repos/sicost/' + pre_year + '/资产总额,负债总额.xlsx'
df_2 = pd.read_excel(xlsx_name)
df_2 = df_2[['COMPANY_NAME', 'RETURN']]
# 为了省事，先直接创建处理好return
# df_2 = df_2[['COMPANY_NAME', 'CURR_YEAR_VALUE_1', 'CURR_YEAR_VALUE_2']]
# df_2['RETURN'] = df_2['CURR_YEAR_VALUE_1'] - df_2['CURR_YEAR_VALUE_2']
# df_2.drop(['CURR_YEAR_VALUE_1'], axis=1, inplace=True)
# df_2.drop(['CURR_YEAR_VALUE_2'], axis=1, inplace=True)

df_2.rename(columns={'RETURN': 'LAST_YEAR_RETURN'}, inplace=True)

xlsx_name = 'D:/repos/sicost/' + month + '/实现利税,利润总额.xlsx'
df_3 = pd.read_excel(xlsx_name)
df_3 = df_3[['COMPANY_NAME', 'CURR_YEAR_VALUE_2']]
df_3.rename(columns={'CURR_YEAR_VALUE_2': 'PROFIT'}, inplace=True)
v = ['COMPANY_NAME']
df_0 = pd.merge(df_1, df_2, on=v, how='left')
df_0 = pd.merge(df_0, df_3, on=v, how='left')
df_0['AVG_RETURN'] = (df_0['LAST_YEAR_RETURN'] + df_0['CURR_YEAR_RETURN']) / 2

def __cal_net_profit(x):
    if x.PROFIT < 0:
        rst = x.PROFIT
    else:
        rst = x.PROFIT * (1 - tax_rate)
    return rst


df_0['NET_PROFIT'] = df_0.apply(lambda x: __cal_net_profit(x), axis=1)
def __cal_roe(x):
    if x.AVG_RETURN < 0:
        rst = -100
    else:
        rst = x.NET_PROFIT / x.AVG_RETURN / month_int * 12
    return rst


df_0['ROE'] = df_0.apply(lambda x: __cal_roe(x), axis=1)
desired_order = ['SERIAL_NUM', 'COMPANY_NAME', 'LAST_YEAR_RETURN', 'CURR_YEAR_RETURN', 'AVG_RETURN', 'PROFIT', 'NET_PROFIT', 'ROE']
df_0 = df_0.reindex(columns=desired_order)
# writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/Sheet3.xlsx')
# df_0.to_excel(writer, sheet_name='Sheet1', index=False)
# writer.save()
# ROE
# xlsx_name = 'D:/repos/sicost/' + month + '/Sheet3.xlsx'
# df_0 = pd.read_excel(xlsx_name)
df_0 = df_0[df_0['COMPANY_NAME'] != '钢铁企业合计']
df_0 = df_0.reset_index(drop=True)

xlsx_name = 'D:/repos/sicost/' + pre_month + '/ROE.xlsx'
df_4 = pd.read_excel(xlsx_name)
df_4 = df_4[['COMPANY_NAME', 'ROE']]
df_4.rename(columns={'ROE': 'LAST_MONTH_ROE'}, inplace=True)
v = ['COMPANY_NAME']
df_0 = pd.merge(df_0, df_4, on=v, how='left')
df_0['DIFF'] = df_0['ROE'] - df_0['LAST_MONTH_ROE']
writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_ROE.xlsx')
df_0.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
xlsx_name = 'D:/repos/sicost/海外/海外数据.xlsx'
df_5 = pd.read_excel(xlsx_name)
df_5 = df_5[df_5['IND_NAME'] == 'ROE']
df_5 = df_5[['COMPANY_NAME', 'DATE_VER', 'IND_VALUE']]
df_5.rename(columns={'IND_VALUE': 'ROE'}, inplace=True)
def __cal_date_ver_new(x):
    rst1 = x.DATE_VER[0:4]
    if x.DATE_VER[4:6] == 'Q1':
        rst2 = '11'
    elif x.DATE_VER[4:6] == 'H1':
        rst2 = '22'
    elif x.DATE_VER[4:6] == 'Q3':
        rst2 = '33'
    elif x.DATE_VER[4:6] == 'H2':
        rst2 = '44'
    else:
        rst2 = '55'
    rst = rst1 + rst2
    return rst


df_5['DATE_VER_NEW'] = df_5.apply(lambda x: __cal_date_ver_new(x), axis=1)
latest_data_ver = df_5.iloc[-1]['DATE_VER_NEW']
df_5_1 = df_5[df_5['DATE_VER_NEW'] == latest_data_ver]
df_5_1.drop(['DATE_VER_NEW'], axis=1, inplace=True)
df_5_1.drop(['DATE_VER'], axis=1, inplace=True)
df_5_1 = df_5_1.reset_index(drop=True)
dict_0 = {}
for index, row in df_5_1.iterrows():
    dict_0['COMPANY_NAME'] = row['COMPANY_NAME']
    dict_0['ROE'] = row['ROE']
    new_row = pd.Series(dict_0)
    df_0 = df_0.append(new_row, ignore_index=True)

# df_out = pd.DataFrame(columns=['百分位', '分位值'])
df_out = pd.DataFrame(columns=['PR', 'PR_VALUE'])
dict_out = {}
for i in range(1, 19):
    # print((i+1)*5)
    percentiles = (i+1) * 5 / 100
    dict_out['PR'] = str(int((i+1) * 5)) + '分位'
    quantiles = df_0['ROE'].quantile(percentiles)
    dict_out['PR_VALUE'] = quantiles
    new_row = pd.Series(dict_out)
    df_out = df_out.append(new_row, ignore_index=True)
writer = pd.ExcelWriter('D:/repos/sicost/' + month + '/NEW_ROE_PR.xlsx')
df_out.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
print('finish')
